const express = require("express");
const router = express.Router();
const db = require("../sql");

// 获取单个商品
router.get("/get/:productId", async (req, res) => {
  try {
    const { productId } = req.params;

    const query = `
        SELECT p.id, p.title, p.price, p.pic, p.createTime,
            COUNT(c.id) AS commentCount,
            (SELECT SUM(quantity) FROM orderItems WHERE productId = p.id) AS salesVolume
        FROM product p
        LEFT JOIN comment c ON p.id = c.productId
        WHERE p.id = ?
        GROUP BY p.id;
    `;

    const product = await db.query(query, [productId]);

    if (product.length > 0) {
      res.status(200).json({ success: true, data: product[0] });
    } else {
      res.status(404).json({ success: false, message: "商品未找到" });
    }
  } catch (error) {
    res
      .status(500)
      .json({ success: false, message: "服务器错误", error: error.message });
  }
});

// 获取商品列表（分页），包括评论数量
router.get("/", async (req, res) => {
  try {
    // 接收分页、排序参数和搜索关键词
    const {
      page = 1,
      limit = 10,
      sortBy = "createTime",
      order = "DESC",
      searchKey = "",
    } = req.query;
    const offset = (page - 1) * limit;

    // 根据请求参数确定排序字段
    const validSortFields = [
      "price",
      "commentCount",
      "salesVolume",
      "createTime",
    ];
    const sortField = validSortFields.includes(sortBy) ? sortBy : "createTime";
    const sortOrder = order.toUpperCase() === "ASC" ? "ASC" : "DESC";

    // 构建查询语句，包括评论数量、销售量以及支持搜索标题
    const query = `
          SELECT p.id, p.title, p.price, p.pic, p.createTime,p.isDeleted ,
              COUNT(c.id) AS commentCount,
              (SELECT SUM(quantity) FROM orderItems WHERE productId = p.id) AS salesVolume
          FROM product p
          LEFT JOIN comment c ON p.id = c.productId
          WHERE p.isDeleted = 0 and p.title LIKE ?
          GROUP BY p.id
          ORDER BY ${sortField} ${sortOrder}
          LIMIT ? OFFSET ?;
      `;
    const countQuery =
      "SELECT COUNT(*) AS total FROM product WHERE isDeleted = 0 and title LIKE ?;";

    // 同时执行两个查询
    const [products, total] = await Promise.all([
      db.query(query, [`%${searchKey}%`, parseInt(limit), parseInt(offset)]),
      db.query(countQuery, [`%${searchKey}%`]),
    ]);

    // 计算总页数
    const totalPages = Math.ceil(total[0].total / limit);

    res.status(200).json({
      success: true,
      data: {
        products,
        currentPage: parseInt(page),
        totalPages,
        totalCount: total[0].total,
      },
    });
  } catch (error) {
    res
      .status(500)
      .json({ success: false, message: "服务器错误", error: error.message });
  }
});

// 获取评论数最多的24条商品数据
router.get("/top", async (req, res) => {
  try {
    const products = await db.query(
      `SELECT p.id, p.title, p.price, p.pic,p.isDeleted, COUNT(c.id) AS commentCount
            FROM product p
            LEFT JOIN comment c ON p.id = c.productId
            where p.isDeleted = 0
            GROUP BY p.id
            ORDER BY commentCount DESC
            LIMIT 24`
    );
    res.json({ success: true, data: products });
  } catch (error) {
    res
      .status(500)
      .json({ success: false, message: "服务器错误", error: error.message });
  }
});

// 更新商品信息
router.put("/", async (req, res) => {
  const { price, title, pic, id } = req.body; // 从请求体中获取商品信息

  try {
    if (id) {
      // 修改逻辑
      // 首先检查商品是否存在
      const checkProduct = await db.query(
        "SELECT * FROM product WHERE id = ?",
        [id]
      );
      if (checkProduct.length === 0) {
        return res.status(404).json({ message: "Product not found" });
      }

      // 更新商品信息
      const sql =
        "UPDATE product SET price = ?, title = ?, pic = ? WHERE id = ?";
      await db.query(sql, [price, title, pic, id]);
      res.json({ message: "Product updated successfully" });
    } else {
      // 新增逻辑
      const sql =
        "INSERT INTO product (price, title, pic,isDeleted) VALUES (?, ?, ?,0)";
      await db.query(sql, [price, title, pic]);
      res.json({ message: "Product added successfully" });
    }
  } catch (error) {
    res.status(500).json({ message: "Error updating product", error });
  }
});

// 删除商品(把isDeleted字段置为1)
router.delete("/:id", async (req, res) => {
  const { id } = req.params;
  try {
    const sql = "UPDATE product SET isDeleted = 1 WHERE id = ?";
    await db.query(sql, [id]);
    res.json({ message: "Product deleted" });
  } catch (error) {
    res.status(500).json({ message: "Error deleting product", error });
  }
});

module.exports = router;
